﻿using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using ChinaDynastyTours.Models;
using System.Threading;
using System;

namespace ChinaDynastyTours.Controls
{
    class TravelOrderCtrl
    {
        #region 获取地接社数据

        /// <summary>
        /// 根据指定的计划号获取一条旅行社费用及预订信息
        /// </summary>
        /// <param name="scheduleId"></param>
        private static DataTable GetTravelExpenseAndOrderData(string scheduleId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string Sql = string.Format(@"SELECT h.*,e.* FROM [fee_TravelOrder] h,fee_Expense e WHERE e.ExpenseID=h.ExpenseID AND e.TourGroupScheduleID={0}",scheduleId);
            DbCommand dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 获取地接预定表数据
        /// </summary>
        /// <param name="tourGroupScheduleId">行程计划ID,每条行程计划对应唯一一条宾馆数据，反过来则不成立</param>
        /// <param name="expenseId">费用标识</param>
        /// <returns></returns>
        internal static DataTable GetTravelOrderData(int tourGroupScheduleId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            //根据行程计划ID，取得‘费用表’与‘宾馆预定信息’表联合数据
            string sql = string.Format(@"SELECT h.[TravelOrderID],
                                                h.[TourgroupID], 
                                                h.[ExpenseID], 
                                                h.[City], 
                                                h.[ServiceName],
                                                h.[SinglePrice],
                                                h.[Amount],
                                                h.[ContractPath],
                                                e.[TypeBelong],
                                                e.[ProviderID],
                                                e.[ProviderName],
                                                e.[Budget],
                                                e.[Status],
                                                e.[ServiceName],
                                                e.[PayTotal]
                                                FROM [fee_TravelOrder] h,fee_Expense e WHERE  h.ExpenseID=e.ExpenseID AND h.TourGroupScheduleID={0}", tourGroupScheduleId);

            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        internal static DataTable GetTravelOrderDataByExpenseId(string expenseId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            //根据费用ID，取得‘费用表’与‘宾馆预定信息’表联合数据
            string sql = string.Format(@"SELECT h.[TravelOrderID],
                                                h.[TourgroupID], 
                                                h.[ExpenseID], 
                                                h.[City], 
                                                h.[ProviderID], 
                                                h.[ProviderName], 
                                                h.[ServiceName],
                                                h.[SinglePrice],
                                                h.[Amount],
                                                h.[ContractPath],
                                                e.[ProviderID],
                                                e.[ProviderName],
                                                e.[Budget]
                                                 FROM [fee_TravelOrder] h,fee_Expense e WHERE  h.ExpenseID=e.ExpenseID AND e.ExpenseID={0}", expenseId);

            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        #endregion

        internal static int CreateTravelOrderAndExpenseData(ExpenseEntity expenseEntity, TravelOrderEntity travelOrderEntity)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sqlExpense = string.Format(@"INSERT INTO fee_Expense(
                                        [TourgroupID],
                                        [ProviderID],
                                        [ProviderName],
                                        [ServiceName],
                                        [Budget],
                                        [PayTotal],
                                        [ApplyPayTotal],
                                        [AdjustAmount],
                                        [Status],
                                        [TypeBelong],
                                        [ApplyPay],[PayStatus],[TourgroupName],[CreateByUserName],[TourGroupScheduleID],[CreateTime],[ExpenseTypeID],[ExpenseType])
                                        VALUES('{0}', {1}, '{2}', '{3}',{4},{5}, {6},{7}, '{8}', '{9}',{10},'{11}','{12}','{13}',{14},'{15}',{16},'{17}');Select SCOPE_IDENTITY();",
                                        expenseEntity.TourgroupID,
                                        expenseEntity.ProviderID,
                                        expenseEntity.ProviderName,
                                        expenseEntity.ServiceName,
                                        expenseEntity.Budget,
                                        expenseEntity.PayTotal,
                                        expenseEntity.ApplyPayTotal,
                                        expenseEntity.AdjustAmount,
                                        expenseEntity.Status,
                                        expenseEntity.TypeBelong,
                                        expenseEntity.ApplyPay,
                                        expenseEntity.PayStatus,
                                        expenseEntity.TourgroupName,
                                        expenseEntity.CreateByUserName,
                                        expenseEntity.TourGroupScheduleID,
                                        expenseEntity.CreateTime,
                                        expenseEntity.ExpenseTypeID,
                                        expenseEntity.ExpenseType);
            string expenseID = db.ExecuteScalar(CommandType.Text, sqlExpense).ToString();
            string sqlHotel = string.Format(@"INSERT INTO fee_TravelOrder(
                                            [TourgroupID], 
                                            [ExpenseID], [TourGroupScheduleID], [City],
                                            [ProviderID], [ProviderName], [ServiceName], 
                                            [SinglePrice], [Amount], [ContractPath])
                                            VALUES('{0}', {1}, {2},'{3}', {4},'{5}','{6}',{7},{8},'{9}')",
                                       travelOrderEntity.TourgroupID,
                                       expenseID,
                                       travelOrderEntity.TourGroupScheduleID,
                                       travelOrderEntity.City,
                                       travelOrderEntity.ProviderID,
                                       travelOrderEntity.ProviderName,
                                       travelOrderEntity.ServiceName,
                                       travelOrderEntity.SinglePrice,
                                       travelOrderEntity.Amount,
                                       travelOrderEntity.ContractPath
                                       );
            return db.ExecuteNonQuery(CommandType.Text, sqlHotel);
        }
        
        /// <summary>
        /// 根据指定的行程计划号，更新地接社预定信息
        /// </summary>
        /// <param name="expense"></param>
        /// <param name="travelOrder"></param>
        internal static int UpdateTravelOrderData(ExpenseEntity expenseEntity, TravelOrderEntity travelOrderEntity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sqlExpense = string.Format(@"UPDATE fee_Expense SET 
                                        [ProviderID]={0}, [ProviderName]='{1}',
                                        [Budget]={2}, [TypeBelong]='{3}',[ServiceName]='{4}'
                                        WHERE ExpenseID={5}",
                                       expenseEntity.ProviderID,
                                       expenseEntity.ProviderName,
                                       expenseEntity.Budget,
                                       expenseEntity.TypeBelong,
                                       expenseEntity.ServiceName,
                                       expenseEntity.ExpenseID
                                    );
            db.ExecuteNonQuery(CommandType.Text, sqlExpense);
            string sqlTravel = string.Format(@"UPDATE fee_TravelOrder SET
                                        [TourgroupID]='{0}', [City]='{1}',
                                        [ProviderID]={2}, [ProviderName]='{3}', [ServiceName]='{4}', 
                                        [SinglePrice]={5}, [Amount]={6}, [ContractPath]='{7}'
                                        WHERE [ExpenseID]={8}",
                                        travelOrderEntity.TourgroupID,
                                        //travelOrderEntity.TourGroupScheduleID,
                                        travelOrderEntity.City,
                                        travelOrderEntity.ProviderID,
                                        travelOrderEntity.ProviderName,
                                        travelOrderEntity.ServiceName,
                                        travelOrderEntity.SinglePrice,
                                        travelOrderEntity.Amount,
                                        travelOrderEntity.ContractPath,
                                        travelOrderEntity.ExpenseID
                                    );
            return db.ExecuteNonQuery(CommandType.Text, sqlTravel);
        }
        /// <summary>
        /// 复制团计划所属的宾馆信息到新的团
        /// </summary>
        /// <param name="newTourGroupId"></param>
        /// <param name="scheduleId"></param>
        /// <returns></returns>
        internal static bool CopyTravelOrder(string newTourGroupId, string newtourgroupName, string oldTourGroupId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DataTable dtNewSchedule = TeamGroupCtrl.GetTourGroupSchedule(newTourGroupId).Tables[0];//获取新团的计划号数据
            DataTable dtOldSchedule = TeamGroupCtrl.GetTourGroupSchedule(oldTourGroupId).Tables[0];//获取旧团的计划号数据
            for (int i = 0; i < dtOldSchedule.Rows.Count; i++)
            {
                ExpenseEntity eEntity = new ExpenseEntity();
                TravelOrderEntity tEntity = new TravelOrderEntity();
                string oldScheduleId = dtOldSchedule.Rows[i]["TourGroupScheduleID"].ToString();
                string newScheduleId = dtNewSchedule.Rows[i]["TourGroupScheduleID"].ToString();

                foreach (DataRow EOrderDr in TravelOrderCtrl.GetTravelExpenseAndOrderData(oldScheduleId).Rows)
                {
                    //费用信息
                    eEntity.AdjustAmount = 0;// decimal.Parse(EOrderDr["AdjustAmount"].ToString());
                    eEntity.ApplyPay = 0;// decimal.Parse(EOrderDr["ApplyPay"].ToString());
                    eEntity.ApplyPayTotal = 0;// decimal.Parse(EOrderDr["ApplyPayTotal"].ToString());
                    eEntity.Budget = decimal.Parse(EOrderDr["Budget"].ToString());
                    eEntity.CreateByUserName = Thread.CurrentPrincipal.Identity.Name;
                    eEntity.CreateTime = DateTime.Now.ToString("yyyy-MM-dd");
                    //eEntity.ExpenseID = int.Parse(EOrderDr["AccompanyRoomNum"].ToString());
                    eEntity.ExpenseTypeID = int.Parse(EOrderDr["ExpenseTypeID"].ToString());
                    eEntity.PayStatus = PayApplyStatusEnum.未付.ToString();//EOrderDr["PayStatus"].ToString();
                    eEntity.PayTotal = 0;// decimal.Parse(EOrderDr["PayTotal"].ToString());
                    eEntity.ProviderID = int.Parse(EOrderDr["ProviderID"].ToString());
                    eEntity.ProviderName = EOrderDr["ProviderName"].ToString();
                    eEntity.Remark = EOrderDr["Remark"].ToString();
                    eEntity.ServiceName = EOrderDr["ServiceName"].ToString();
                    eEntity.Status = ExpenseStatusEnum.计划.ToString();//EOrderDr["Status"].ToString();
                    eEntity.TourgroupID = newTourGroupId;
                    eEntity.TourgroupName = newtourgroupName;
                    eEntity.TourGroupScheduleID = int.Parse(newScheduleId);
                    eEntity.TypeBelong = EOrderDr["TypeBelong"].ToString();
                    eEntity.WipeOffMethod = EOrderDr["WipeOffMethod"].ToString();
                    //预订信息
                    tEntity.Amount = int.Parse(EOrderDr["Amount"].ToString());
                    tEntity.City = EOrderDr["City"].ToString();
                    tEntity.ContractPath = EOrderDr["ContractPath"].ToString();
                    //tEntity.ExpenseID = int.Parse(EOrderDr["ExpenseID"].ToString());
                    tEntity.ProviderID = int.Parse(EOrderDr["ProviderID"].ToString());
                    tEntity.ProviderName = EOrderDr["ProviderName"].ToString();
                    tEntity.ServiceName = EOrderDr["ServiceName"].ToString();
                    tEntity.SinglePrice = decimal.Parse(EOrderDr["SinglePrice"].ToString());
                    tEntity.TourgroupID = newTourGroupId;
                    tEntity.TourGroupScheduleID = int.Parse(newScheduleId);
                    CreateTravelOrderAndExpenseData(eEntity, tEntity);
                }
            }
            return true;
        }
        /// <summary>
        /// 删除指定计划号的地接社预定信息
        /// </summary>
        /// <param name="tourGroupScheduleID"></param>
        /// <returns></returns>
        public static int DeleteTravelOrder(int tourGroupScheduleID)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format("select expenseid from fee_TravelOrder where TourGroupScheduleID={0}", tourGroupScheduleID);
            DbCommand dbCmd = db.GetSqlStringCommand(sql);

            object expenseObj = db.ExecuteScalar(dbCmd);

            if (expenseObj == null)
                return 0;

            sql = string.Format(@"DELETE FROM fee_TravelOrder WHERE TourGroupScheduleID={0};
                                         DELETE FROM fee_expense WHERE  expenseid={1};",//同时删除费用和地接社相关内容
                                         tourGroupScheduleID,
                                         expenseObj.ToString());
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }


    }
}
